<?php
namespace app\api\controller;

use app\common\controller\Api;
use app\common\model\User as UserModel;
use app\common\model\EntrustUserRel as EntrustUserRel;
use think\Db;
use think\facade\Log;

class Apartment extends Api
{
    /**
     * 获取学生公寓用品领用信息
     * @return array
     */
    public function getSuppliesStatus()
    {
        $platform  = input('param.platform', 1);      // 1就是h5登录（h5端和微信公众号端），2就是微信小程序登录，3是支付宝小程序，4是app，5是pc
        $key  = input('param.key', 1);      // 搜索关键字
        
        // 首先判断学生所在的部门，如果是中专的学生就返回提醒
        // $sqldept = "select bmdm from zlzhsf.pxsdm where (xh = '" . $key . "' or SFZH = '" . $key . "') and bmdm like '006%'";
        // $listdept = Db::query($sqldept);
        // if ($listdept && count($listdept) > 0) {
        //     return error_code(20004);
        // }

        $sql = "select a.XH, a.XM, b.BMMC, c.BJMC, d.sfqjdm, e.LQSJ, e.FFR from zlzhsf.pxsdm a join ".
        "zlzhsf.pbmdm b on a.bmdm = b.bmdm join ".
        "zlzhsf.pbjdm c on a.bjdm = c.bjdm left join ". 
        "(select a.xh, a.sfqjdm from( select xh, sfqjdm, sfxmdm, sum(je) je from zlzhsf.sf_sfdb a, zlzhsf.sf_sfdmx b, zlzhsf.pxtcs c where a.sfpjnm = b.sfpjnm and b.sfxmdm = '05' and b.sfqjdm = c.val and c.csmc = 'sfqj' and iszf = 0 group by xh, sfqjdm, sfxmdm)a, zlzhsf.sf_ysk b where a.xh = b.xh and a.sfqjdm = b.sfqjdm and a.sfxmdm = b.sfxmdm and a.je = b.ysje ) d on a.xh = d.xh left join ".
        "(select xh, LQSJ, FFR from zlzhsf.pwply a, zlzhsf.pxtcs b where a.sfqjdm = b.val and b.csmc = 'sfqj') e on a.xh = e.xh ".
        "where a.xh = '" . $key . "' or a.SFZH = '" . $key . "'";
  
        $list = Db::query($sql);
        if (!$list || count($list) == 0) {
            return error_code(20001);
        }

        $isPay = '已缴费';
        $isReceive = '已领取';
        if(!$list[0]['sfqjdm']){
            $isPay = '未缴费';
        }

		if(!$list[0]['FFR']){
            $isReceive = '未领取';
		}
        
        $result = [
            'status' => true,
            'data'   => ['account' => $list[0]['XH'], 'name' =>  $list[0]['XM'], 'collegeName' => $list[0]['BMMC'], 'className' => $list[0]['BJMC'], 'isPay' => $isPay, 'isReceive' => $isReceive, 'LQSJ' => $list[0]['LQSJ'], 'FFR' => $list[0]['FFR'], ],
            'msg'    => '查询成功'
        ];

        return $result;
    }

    /**
     * 设置学生公寓用品领用信息
     * @return array
     */
    public function setSuppliesStatus()
    {
        $platform  = input('param.platform', 1);      // 1就是h5登录（h5端和微信公众号端），2就是微信小程序登录，3是支付宝小程序，4是app，5是pc
        $account  = input('param.account', 1);      // 学号
        
        $sql = "select a.XH, a.XM, b.BMMC, c.BJMC, d.sfqjdm, e.LQSJ, e.FFR from zlzhsf.pxsdm a join ".
        "zlzhsf.pbmdm b on a.bmdm = b.bmdm join ".
        "zlzhsf.pbjdm c on a.bjdm = c.bjdm left join ". 
        "(select a.xh, a.sfqjdm from( select xh, sfqjdm, sfxmdm, sum(je) je from zlzhsf.sf_sfdb a, zlzhsf.sf_sfdmx b, zlzhsf.pxtcs c where a.sfpjnm = b.sfpjnm and b.sfxmdm = '05' and b.sfqjdm = c.val and c.csmc = 'sfqj' and iszf = 0 group by xh, sfqjdm, sfxmdm)a, zlzhsf.sf_ysk b where a.xh = b.xh and a.sfqjdm = b.sfqjdm and a.sfxmdm = b.sfxmdm and a.je = b.ysje ) d on a.xh = d.xh left join ".
        "zlzhsf.pwply e on d.sfqjdm = e.sfqjdm and a.xh = e.xh ".
        "where a.xh = '" . $account . "' or a.SFZH = '" . $account . "'";

        $list = Db::query($sql);
        if (!$list || count($list) == 0) {
            return error_code(20001);
        }
 
        $isPay = '已缴费';
        $isReceive = '已领取';
        if(!$list[0]['sfqjdm']) { 
            return error_code(20002);
        }
        
        if($list[0]['FFR']) {
            return error_code(20003);
        }

        $userModel = new userModel();
        $userInfo   = $userModel->field('u.mobile, u.truename, r.dormId, er.userMobile')->alias('u')
                                ->leftjoin('DormUserRel r', 'u.mobile = r.mobile')
                                ->leftjoin('EntrustUserRel er', 'u.mobile = er.entrustUserMobile')
                                ->where('u.id', 'eq', $this->userId)->find();

        if(!isset($userInfo['dormId'])){
            return error_code(20005);
        }

        $mgrMobile = $userInfo['mobile'];
        if(isset($userInfo['userMobile'])){
            $mgrMobile = $userInfo['userMobile'];
        }

        $insertSql = "insert into zlzhsf.pwply(xh, sfqjdm, lqsj, ffrMobile, ffr, dormId, mgrUserMobile) VALUES('".$account."', '".$list[0]['sfqjdm']."', '".date('Y-m-d H:i:s',time())."', '".$userInfo['mobile']."', '".$userInfo['truename']."', '".$userInfo['dormId']."', '".$mgrMobile."')";
		 
        // 设置领用状态
        Db::query($insertSql);
 
        $result = [
            'status' => true, 
            'msg'    => '领取成功'
        ];

        return $result;
    }

    /**
     * 获取当前操作员所负责的宿舍楼
     * @return array
     */
    public function getDormInfo()
    {
        $platform  = input('param.platform', 1);      // 1就是h5登录（h5端和微信公众号端），2就是微信小程序登录，3是支付宝小程序，4是app，5是pc
        $key  = input('param.key', 1);      // 搜索关键字
         

        $sql = "select c.id, c.Dormname ".
        "from zl_user a, zl_dorm_user_rel b, zl_Dorm c ".
        "where a.mobile = b.mobile and b.DormId = c.id and a.id = '" . $this->userId . "'"; 
   
        $list = Db::query($sql);
        if (!$list || count($list) == 0) {
            return error_code(20101);
        }
 
        $result = [
            'status' => true,
            'data'   => $list,
            'msg'    => '查询成功'
        ];

        return $result;
    }

    /**
     * 根据管理员、时间获取物品发放清单
     * @return array
     */
    public function getSuppliesInfo()
    {
        $platform  = input('param.platform', 1);      // 1就是h5登录（h5端和微信公众号端），2就是微信小程序登录，3是支付宝小程序，4是app，5是pc
        $operator  = input('param.operator', '');     // 搜索关键字
        $all = input('param.all', '');          // 搜索关键字
        $date  = input('param.date', '');             // 搜索关键字
        
        // 如果日期不存在返回错误
        if ($date == '') {
            return error_code(20102);
        }

        // 日期检测
        $times = explode("-", $date);
        if(count($times) != 3) {
            return error_code(20102);
        }

        if(strlen($times[1]) == 1) {
            $times[1] = "0" . $times[1];
        }

        if(strlen($times[2]) == 1) {
            $times[2] = "0" . $times[2];
        }

        $userModel = new userModel();
        $userInfo = $userModel->getUserInfo($this->userId);
 
        $date = $times[0].'-'.$times[1].'-'.$times[2];
  
        $sqlAll = '';
        if($all == '1') {
            $sqlAll = "select * From zlzhsf.pwply where FFRMobile in ('".$operator."') or mgrUserMobile = '".$userInfo['mobile']."'";
        } else {
            $sqlAll = "select * From zlzhsf.pwply where FFRMobile in ('".$operator."')";
        }
        $listAll = Db::query($sqlAll);

        $sqlQuery = "";
        if($all == '1') {
            $sqlQuery = "select A.ID_, A.FFR, B.XH, B.XM, SUBSTR(A.LQSJ, 12, 8) LQSJ From zlzhsf.pwply a, zlzhsf.pxsdm b where a.xh = b.xh and substr(lqsj, 1, 10) = '".$date."' and (FFRMobile in ('".$operator."') or mgrUserMobile = '".$userInfo['mobile']."') order by SUBSTR(A.LQSJ, 12, 8)";
        } else {
            $sqlQuery = "select A.ID_, A.FFR, B.XH, B.XM, SUBSTR(A.LQSJ, 12, 8) LQSJ From zlzhsf.pwply a, zlzhsf.pxsdm b where a.xh = b.xh and substr(lqsj, 1, 10) = '".$date."' and FFRMobile in ('".$operator."') order by SUBSTR(A.LQSJ, 12, 8)";
        }
        $listQuery = Db::query($sqlQuery);

        $data = [
            'allNum' => count($listAll),
            'num' => count($listQuery),
            'list' => $listQuery
        ];

        $result = [
            'status' => true,
            'data'   => $data,
            'msg'    => '查询成功'
        ];

        return $result;
    }
    
    /**
     * 获取物品发放清单
     * @return array
     */
    public function getSuppliesInfoEx()
    {
        $userModel = new userModel();
        $userInfo = $userModel->getUserInfo($this->userId);
        $sqlAll = "select a.id_
            From zlzhsf.pwply a, zlzhsf.pxtcs b
            where a.sfqjdm = b.val and b.csmc = 'sfqj' and (FFRMobile = '".$userInfo['mobile']."' or mgrUserMobile = '".$userInfo['mobile']."')";
        $listAll = Db::query($sqlAll);

        $listToday = "select a.id_
            From zlzhsf.pwply a, zlzhsf.pxtcs b
            where a.sfqjdm = b.val and b.csmc = 'sfqj' and substr(lqsj, 1, 10) = '". date('Y-m-d', time()) ."' and (FFRMobile = '".$userInfo['mobile']."' or mgrUserMobile = '".$userInfo['mobile']."')";
        $listToday = Db::query($listToday);

        $sqlQuery = "select sfqjdm, substr(lqsj, 1, 10) date, count(a.id_) as num 
            From zlzhsf.pwply a, zlzhsf.pxtcs b
            where  a.sfqjdm = b.val and b.csmc = 'sfqj' and (FFRMobile = '".$userInfo['mobile']."' or mgrUserMobile = '".$userInfo['mobile']."')
            group by sfqjdm, substr(lqsj, 1, 10)
            order by substr(lqsj, 1, 10) desc";
        
        $listQuery = Db::query($sqlQuery);

        $data = [
            'allNum' => count($listAll),
            'num' => count($listToday),
            'list' => $listQuery
        ];

        $result = [
            'status' => true,
            'data'   => $data,
            'msg'    => '查询成功'
        ];

        return $result;
    }

    /**
     * 根据时间获取物品发放清单
     * @return array
     */
    public function suppliesDetail()
    {
        $date  = input('param.date', '');             // 搜索关键字
        
        // 如果日期不存在返回错误
        if ($date == '') {
            return error_code(20102);
        }

        $userModel = new userModel();
        $userInfo = $userModel->getUserInfo($this->userId);
        $sqlQuery = "select A.ID_, A.FFR, B.XH, B.XM, SUBSTR(A.LQSJ, 12, 8) LQSJ 
            From zlzhsf.pwply a, zlzhsf.pxsdm b, zlzhsf.pxtcs c
            where a.sfqjdm = c.val and a.xh = b.xh and c.csmc = 'sfqj' and (FFRMobile = '".$userInfo['mobile']."' or mgrUserMobile = '".$userInfo['mobile']."')
            and substr(lqsj, 1, 10) = '". $date ."' 
            order by SUBSTR(A.LQSJ, 12, 8)";
        
        $listQuery = Db::query($sqlQuery);
 
        $data = [
            'list' => $listQuery
        ];

        $result = [
            'status' => true,
            'data'   => $data,
            'msg'    => '查询成功'
        ];

        return $result;
    }

    /**
     * 根据时间导出物品发放清单
     * @return array
     */
    public function exportDailyData()
    {
        $date  = input('param.date', '');             // 搜索关键字
        
        // 如果日期不存在返回错误
        if ($date == '') {
            return error_code(20102);
        }

        $userModel = new userModel();
        $userInfo = $userModel->getUserInfo($this->userId);
        $sqlQuery = "select A.ID_, A.FFR, B.XH, B.XM, SUBSTR(A.LQSJ, 12, 8) LQSJ 
            From zlzhsf.pwply a, zlzhsf.pxsdm b, zlzhsf.pxtcs c
            where a.sfqjdm = c.val and a.xh = b.xh and c.csmc = 'sfqj' and (FFRMobile = '".$userInfo['mobile']."' or mgrUserMobile = '".$userInfo['mobile']."')
            and substr(lqsj, 1, 10) = '". $date ."' 
            order by SUBSTR(A.LQSJ, 12, 8)";
        
        $listQuery = Db::query($sqlQuery);

        $url = '';
        // 查询导出内容
        $template = config('zlserver.apartmentdailydata_export_templete');     //使用模板
        $objPHPExcel = \PHPExcel_IOFactory::load($template);     //加载excel文件,设置模板 
        $objWriter = new \PHPExcel_Writer_Excel2007($objPHPExcel);  //设置保存版本格式  
        
        //接下来就是写数据到表格里面去  
        $objActSheet = $objPHPExcel->getActiveSheet();
        $i = 2;
        foreach ($listQuery as $key => $val) {
            //这里是设置单元格的内容  
            $objActSheet->setCellValue('A'.$i, $val['XH']); 
            $objActSheet->setCellValue('B'.$i, $val['XM']); 
            $objActSheet->setCellValue('C'.$i, $val['FFR']); 
            $objActSheet->setCellValue('D'.$i, $val['LQSJ']); 

            $i++;
        }
        
        $file_name = "apartmentdailydata-xls-" . date("YmdHis", time()) . ".xls";
        $generalPath = get_hash_dir($file_name);
        $filePath  = defined("FILE_PATH") ? FILE_PATH : ROOT_PATH . 'public' . DS . 'static' . DS . 'file' . $generalPath;
        if (!is_dir($filePath)) {
            @mkdirs($filePath, 0777, true);
        } 
        $fullFilePath = $filePath . $file_name;
        // 1.保存至本地Excel表格  
        $objWriter->save($fullFilePath);
              
        // 返回下载链接
        $url = "https://zlcw.zhongliansoft.com/static/file" . $generalPath . $file_name;

        $return_data = array(
            'status' => true,
            'msg'    => '导出成功',
            'url'    => $url
        );

        return $return_data;
    }
    
    /**
     * 根据学院获取物品发放清单
     * @return array
     */
    public function getCollegeSuppliesInfo()
    {
        $userModel = new userModel();
        $userInfo = $userModel->getUserInfo($this->userId);
        $sqlAll = "select a.id_
            From zlzhsf.pwply a, zlzhsf.pxtcs b, zl_user c
            where a.sfqjdm = b.val and b.csmc = 'sfqj' and a.mgrUserMobile = c.mobile and c.deptId = '".$userInfo['deptId']."'";
        $listAll = Db::query($sqlAll);

        $listToday = "select a.id_
            From zlzhsf.pwply a, zlzhsf.pxtcs b, zl_user c
            where a.sfqjdm = b.val and b.csmc = 'sfqj' and substr(lqsj, 1, 10) = '". date('Y-m-d', time()) ."' and a.mgrUserMobile = c.mobile and c.deptId = '".$userInfo['deptId']."'";
        $listToday = Db::query($listToday);

        $sqlQuery = "select c.mobile, c.truename, count(a.id_) as num 
            From zlzhsf.pwply a, zlzhsf.pxtcs b, zl_user c
            where  a.sfqjdm = b.val and b.csmc = 'sfqj' and a.mgrUserMobile = c.mobile and c.deptId = '".$userInfo['deptId']."'
            group by c.mobile
            order by count(a.id_) desc";
        
        $listQuery = Db::query($sqlQuery);

        $data = [
            'allNum' => count($listAll),
            'num' => count($listToday),
            'list' => $listQuery
        ];

        $result = [
            'status' => true,
            'data'   => $data,
            'msg'    => '查询成功'
        ];

        return $result;
    }

    /**
     * 根据辅导员获取物品发放清单
     * @return array
     */
    public function getCollegeSuppliesDetail()
    {
        $mobile  = input('param.mobile', '');             // 搜索关键字
        
        // 如果日期不存在返回错误
        if ($mobile == '') {
            return error_code(20104);
        }

        $sqlQuery = "select A.ID_, A.FFR, B.XH, B.XM, A.LQSJ 
            From zlzhsf.pwply a, zlzhsf.pxsdm b, zlzhsf.pxtcs c
            where a.sfqjdm = c.val and a.xh = b.xh and c.csmc = 'sfqj' and (FFRMobile = '".$mobile."' or mgrUserMobile = '".$mobile."')
            order by A.LQSJ";
        
        $listQuery = Db::query($sqlQuery);
 
        $data = [
            'list' => $listQuery
        ];

        $result = [
            'status' => true,
            'data'   => $data,
            'msg'    => '查询成功'
        ];

        return $result;
    }

    /**
     * 根据辅导员导出物品发放清单
     * @return array
     */
    public function exportCollegeData()
    {
        $mobile  = input('param.mobile', '');             // 搜索关键字
        
        // 如果日期不存在返回错误
        if ($mobile == '') {
            return error_code(20104);
        }

        $sqlQuery = "select A.ID_, A.FFR, B.XH, B.XM, A.LQSJ 
            From zlzhsf.pwply a, zlzhsf.pxsdm b, zlzhsf.pxtcs c
            where a.sfqjdm = c.val and a.xh = b.xh and c.csmc = 'sfqj' and (FFRMobile = '".$mobile."' or mgrUserMobile = '".$mobile."')
            order by A.LQSJ";
        
        $listQuery = Db::query($sqlQuery);

        $url = '';
        // 查询导出内容
        $template = config('zlserver.apartmentdailydata_export_templete');     //使用模板
        $objPHPExcel = \PHPExcel_IOFactory::load($template);     //加载excel文件,设置模板 
        $objWriter = new \PHPExcel_Writer_Excel2007($objPHPExcel);  //设置保存版本格式  
        
        //接下来就是写数据到表格里面去  
        $objActSheet = $objPHPExcel->getActiveSheet();
        $i = 2;
        foreach ($listQuery as $key => $val) {
            //这里是设置单元格的内容  
            $objActSheet->setCellValue('A'.$i, $val['XH']); 
            $objActSheet->setCellValue('B'.$i, $val['XM']); 
            $objActSheet->setCellValue('C'.$i, $val['FFR']); 
            $objActSheet->setCellValue('D'.$i, $val['LQSJ']); 

            $i++;
        }
        
        $file_name = "apartmentcollegedata-xls-" . date("YmdHis", time()) . ".xls";
        $generalPath = get_hash_dir($file_name);
        $filePath  = defined("FILE_PATH") ? FILE_PATH : ROOT_PATH . 'public' . DS . 'static' . DS . 'file' . $generalPath;
        if (!is_dir($filePath)) {
            @mkdirs($filePath, 0777, true);
        } 
        $fullFilePath = $filePath . $file_name;
        // 1.保存至本地Excel表格  
        $objWriter->save($fullFilePath);
              
        // 返回下载链接
        $url = "https://zlcw.zhongliansoft.com/static/file" . $generalPath . $file_name;

        $return_data = array(
            'status' => true,
            'msg'    => '导出成功',
            'url'    => $url
        );

        return $return_data;
    }
    
    /**
     * 获取管理员所负责的宿舍楼
     * @return array
     */
    public function getMgrDormInfo()
    {
        $platform  = input('param.platform', 1);      // 1就是h5登录（h5端和微信公众号端），2就是微信小程序登录，3是支付宝小程序，4是app，5是pc
         
        $sql = "select '0' id, '全部宿舍楼' Dormname union select d.id, d.Dormname from 
            zl_user a, zl_entrust_user_rel b, zl_dorm_user_rel c, zl_Dorm d
            where a.mobile = b.userMobile and b.entrustUserMobile = c.mobile and c.DormId = d.id and a.id = '" . $this->userId . "' order by id"; 
   
        $list = Db::query($sql);
        if (!$list || count($list) == 0) {
            return error_code(20101);
        }
 
        $result = [
            'status' => true,
            'data'   => $list,
            'msg'    => '查询成功'
        ];

        return $result;
    }

    /**
     * 获取管理员所负责的管理员信息
     * @return array
     */
    public function getMgrOperatorsInfo()
    {
        $platform  = input('param.platform', 1);      // 1就是h5登录（h5端和微信公众号端），2就是微信小程序登录，3是支付宝小程序，4是app，5是pc
        
        $sql = "select '0' id, '全部管理员' truename 
        union select mobile, truename from zl_user a where id = '" . $this->userId . "'
        union select c.mobile, c.truename from zl_user a, zl_entrust_user_rel b, zl_user c where a.id = '" . $this->userId . "' and a.mobile = b.userMobile and b.entrustUserMobile = c.mobile";

        $list = Db::query($sql);
        if (!$list || count($list) == 0) {
            return error_code(20101);
        }
 
        $result = [
            'status' => true,
            'data'   => $list,
            'msg'    => '查询成功'
        ];

        return $result;
    }
    

}
